﻿<%@ WebHandler Language="C#" Class="ZSWIPInfoReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;

/**
 * Description: 装配试车厂在制品报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.11.25
 **/

public class ZSWIPInfoReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"                   
SELECT f.factoryname,m.mfgordername,ot.ordertypename,u.attributevalue,pb.productname,pf.productfamilyname,
       m.plannedstartdate,m.plannedcompletiondate,m.mfgorderid,ep.workflowid,
       CASE WHEN m.plannedcompletiondate IS NULL THEN 0 ELSE TRUNC(SYSDATE-m.plannedcompletiondate,1) END overdays
FROM mfgorder m
INNER JOIN
(
    SELECT ep.mfgorderid, DECODE(ep.workflowstepid,NULL,ep.workflowid,'0') workflowid 
    FROM enginepartplan ep 
    GROUP BY ep.mfgorderid,DECODE(ep.workflowstepid,NULL,ep.workflowid,'0')
    ORDER BY ep.mfgorderid,DECODE(ep.workflowstepid,NULL,ep.workflowid,'0')
) ep ON m.mfgorderid=ep.mfgorderid
LEFT JOIN userattribute u ON m.mfgorderid=u.parentid 
LEFT JOIN productbase pb1 ON m.productbaseid=pb1.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,m.productid)
LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
LEFT JOIN productworkflowmapping pw ON p.productid=pw.productid
LEFT JOIN workflowproductfamilylist wff ON wff.workflowid = pw.workflowid
LEFT JOIN productfamily pf ON wff.productfamilylistid = pf.productfamilyid
LEFT JOIN containerinstore ct ON ct.aufnr = m.mfgordername
LEFT JOIN orderstatus os ON os.orderstatusid = m.orderstatusid
LEFT JOIN factory f ON f.factoryid = m.factoryid
WHERE u.userattributename='发动机' AND ct.containerinstoreid IS NULL AND NVL(os.orderstatusname,'打开') <> '关闭'";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND m.factoryid = '{0}'", jObject["factoryid"].ToString());
        }
        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND u.attributevalue = '{0}'", jObject["engineno"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND m.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //订单类型
        if (jObject["ordertypename"] != null && jObject["ordertypename"].ToString() != "")
        {
            sql += string.Format(" AND ot.ordertypename = '{0}'", jObject["ordertypename"].ToString());
        }

        //计划开始时间
        if (jObject["startdate"] != null && jObject["startdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["startdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND m.plannedstartdate >= to_date('{0}','yyyy-MM-dd')", arr[0].ToString());
                sql += string.Format(" AND m.plannedstartdate <= to_date('{0}','yyyy-MM-dd')", arr[1].ToString());
            }
        }

        //计划完成时间
        if (jObject["finishdate"] != null && jObject["finishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["finishdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND m.plannedcompletiondate >= to_date('{0}','yyyy-MM-dd')", arr[0].ToString());
                sql += string.Format(" AND m.plannedcompletiondate <= to_date('{0}','yyyy-MM-dd')", arr[1].ToString());
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;
        dtResult.Columns.Add("RunningStep");//进行中阶段/工序
        dtResult.Columns.Add("FinishRate");//完成情况
        foreach (DataRow dr in dtResult.Rows)
        {
            string mfgorderId = dr["mfgorderId"].ToString();
            string Workflowid = dr["workflowid"].ToString();
            string RunningStep = string.Empty;
            dr["FinishRate"] = GetMfgOrderStepData(mfgorderId, Workflowid, out RunningStep);
            dr["RunningStep"] = RunningStep;
            if (RunningStep == string.Empty)
            {
                dr["overdays"] = 0;
            }
            if (Convert.ToDecimal(dr["overdays"].ToString()) < 0)
            {
                dr["overdays"] = 0;
            }
        }
        dtResult.AcceptChanges();
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    private string GetMfgOrderStepData(string mfgorderId, string Workflowid, out string RunningStep)
    {
        StringBuilder sb = new StringBuilder();
        sb.AppendLine(" SELECT ep.mfgorderid,ep.containername,ep.workflowstepid,wfs.workflowstepname, ep.usecompleted,");
        sb.AppendLine(" NVL(ep.containerid,ep.enginecontainerid) containerid,ep.workflowid");
        sb.AppendLine(" FROM enginepartplan ep ");
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid");
        sb.AppendFormat(" WHERE ep.mfgorderid='{0}'", mfgorderId);
        sb.AppendLine(" ORDER BY wfs.sequence ASC ");
        DataTable dt = dal.GetDataTable(sb.ToString());
        DataView dv = dt.DefaultView;
        DataTable dtRes = new DataTable();

        RunningStep = string.Empty;
        string FinishRate = string.Empty;
        int TotalCount = 0;
        int FinishCount = 0;
        if (Workflowid == "0")
        {
            //非施工单,判断订单是否有未完成的，无则当前阶段，完成率，超期天数为空
            dv.RowFilter = " workflowstepid is not null";
            dtRes = dv.ToTable();
            TotalCount = dtRes.Rows.Count;

            dv = dtRes.DefaultView;
            dv.RowFilter = " usecompleted='0'";

            FinishCount = TotalCount - dv.ToTable().Rows.Count;
            FinishRate = FinishCount.ToString() + "/" + TotalCount.ToString();
            if (dv.ToTable().Rows.Count == 0)
            {
                RunningStep = string.Empty;
            }
            else
                RunningStep = dv.ToTable().Rows[0]["workflowstepname"].ToString();
        }
        else
        {
            //施工单,获取总的检验完成笔数
            dv.RowFilter = string.Format(" Workflowid='{0}'", Workflowid);
            dtRes = dv.ToTable();
            DataTable dtWf = GetStepAndStepInfoData(Workflowid);
            string Containerid = dtRes.Rows[0]["Containerid"].ToString();
            DataTable dtFinish = GetFinishData(Containerid);
            FinishCount = dtFinish.Rows.Count;
            FinishRate = FinishCount.ToString() + "/" + dtWf.Rows.Count.ToString();
            if (dtRes.Rows[0]["usecompleted"].ToString() == "1")
            {
                RunningStep = string.Empty;
            }
            else
            {
                foreach (DataRow dr in dtWf.Rows)
                {
                    string workflowstepid = dr["workflowstepid"].ToString();
                    string stepinfoid = string.Empty;
                    if (dr["stepinfoid"] != DBNull.Value)
                    {
                        stepinfoid = dr["stepinfoid"].ToString();
                    }

                    if (stepinfoid == string.Empty)
                    {
                        if (dtFinish.Select(string.Format("workflowstepid='{0}'", workflowstepid)).Length == 0)
                        {
                            RunningStep = dr["workflowstepname"].ToString();
                            break;
                        }

                    }
                    else
                    {
                        if (dtFinish.Select(string.Format("workflowstepid='{0}' and stepinfoid='{1}'", workflowstepid, stepinfoid)).Length == 0)
                        {
                            RunningStep = dr["workflowstepname"].ToString() + "/" + dr["stepinfoname"].ToString();
                            break;
                        }

                    }

                }

            }
        }
        return FinishRate;
    }

    //获取检验完成数
    DataTable GetFinishData(string Containerid)
    {
        string sql = " SELECT cci.containerid,cci.containercheckinfoid,cci.workflowstepid,cci.stepinfoid ";
        sql += " FROM containercheckinfo cci WHERE  cci.isuse=0 ";
        sql += string.Format(" AND cci.containerid in ('{0}')", Containerid.Replace(",", "','"));
        DataTable dt = dal.GetDataTable(sql);
        return dt;
    }

    //获取工步及工序信息 Modify by Yuxj20190227
    DataTable GetStepAndStepInfoData(string workflowid)
    {
        string sql = @"
                        SELECT wfs.workflowid,wfs.workflowstepname specname,sib.stepinfoname,s.description,wfs.workflowstepid,s.stepinfoid,
                             wfs.workflowstepid || s.stepinfoid stepinfo, wfs.workflowstepname || '--' || s.description workflowstepname,
                             NVL(t.teamname, w.workcentername) teamname,w.workcentername,
                             wfs.sequence || '--'  || wfs.description || '--' || s.description specdesc
                        FROM workflowstep wfs
                        LEFT JOIN team t ON wfs.teamid = t.teamid
                        LEFT JOIN workcenter w ON wfs.workcenterid = w.workcenterid
                        LEFT JOIN stepinfo s ON wfs.workflowstepid = s.workflowstepid
                        LEFT JOIN stepinfobase sib ON sib.stepinfobaseid = s.stepinfobaseid ";
        sql += string.Format(" WHERE wfs.workflowid='{0}'", workflowid);
        sql += " order by wfs.sequence asc";
        DataTable dt = dal.GetDataTable(sql);
        return dt;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns.Add("RunningStep");
        dt.Columns.Add("FinishRate");
        foreach (DataRow dr in dt.Rows)
        {
            string mfgorderId = dr["mfgorderId"].ToString();
            string Workflowid = dr["workflowid"].ToString();
            string RunningStep = string.Empty;
            dr["FinishRate"] = GetMfgOrderStepData(mfgorderId, Workflowid, out RunningStep);
            dr["RunningStep"] = RunningStep;
            if (RunningStep == string.Empty)
            {
                dr["overdays"] = 0;
            }
            if (Convert.ToDecimal(dr["overdays"].ToString()) < 0)
            {
                dr["overdays"] = 0;
            }
        }
        dt.AcceptChanges();
        dt.Columns.Remove("MFGORDERID");
        dt.Columns.Remove("WORKFLOWID");
        dt.Columns["factoryname"].Caption = "分厂";
        dt.Columns["MFGORDERNAME"].Caption = "订单号";
        dt.Columns["ORDERTYPENAME"].Caption = "任务类型";
        dt.Columns["ATTRIBUTEVALUE"].Caption = "发动机号";
        dt.Columns["PRODUCTNAME"].Caption = "物料号";
        dt.Columns["PRODUCTFAMILYNAME"].Caption = "型别";
        dt.Columns["PLANNEDSTARTDATE"].Caption = "计划开工时间";
        dt.Columns["PLANNEDCOMPLETIONDATE"].Caption = "计划完成时间";
        dt.Columns["OVERDAYS"].Caption = "超期天数";
        dt.Columns["RunningStep"].Caption = "进行中阶段/工序";
        dt.Columns["FinishRate"].Caption = "完成情况";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "在制品统计报表");
    }
}